from django.shortcuts import render
from django.http import HttpResponse
from .models import Student, Score, Course, Teacher
from django.db import models
from django.db.models import Q


def index(request):
    # 1.查询平均成绩大于60的同学id和平均成绩
    students = Student.objects.annotate(avg=models.Avg("score__score")).filter(avg__gte=85).values("id", "avg")
    for student in students:
        print(student)
    return HttpResponse("查询平均成绩大于60的同学id和平均成绩")


def index1(request):
    # 所有同学的id，姓名，选课的书，总成绩
    students = Student.objects.annotate(course_num=models.Count("score__id"), sum_score=models.Sum("score__score")).values("id", "name", "course_num", "sum_score")
    for student in students:
        print(student)
    return HttpResponse("所有同学的id，姓名，选课的书，总成绩")


def index2(request):
    # 姓d的老师
    teachers = Teacher.objects.filter(name__startswith="d").count()
    print(teachers)
    return HttpResponse("姓d的老师")


def index3(request):
    # 查询所有没有学过b老师的学生的id，姓名
    students = Student.objects.exclude(score__course__teacher__name="bob").values("id", "name")
    for student in students:
        print(student)
    return HttpResponse("查询所有没有学过b老师的学生的id，姓名")


def index4(request):
    # 查询学过课程为1和2的同学
    students = Student.objects.filter(score__course__in=[1, 2]).values("id", "name").distinct()
    for student in students:
        print(student)
    return HttpResponse("查询学过课程为1和2的同学，姓名")


def index5(request):
    # 查询学过a老师所有课的学生
    students = Student.objects.annotate(num=models.Count("score_course"), filter=Q())
    # for student in students:
    print(students)
    return HttpResponse("hello")

